package com.key.controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;

import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;

import com.key.model.LoginHistory;
import com.key.model.User;
import com.key.tools.ConnectionPool;

@Controller
@RequestMapping("/user")
public class UserController {
	@RequestMapping("/query")
	public ModelAndView query(User user, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<User> list = new ArrayList<User>();
		try {
			int index = user.getPageNum();
			int number = user.getEveryPage();
			int startindex = (index - 1) * number;
			String sql = "select * from t_user u left join t_group g on u.group_id = g.id where username <> 'admin' and username like '%"
					+ user.getLoginName()
					+ "%' and name like '%"
					+ user.getName() + "%' and u.status = '1'";
			if (!user.getGroupId().equals("")
					&& !user.getGroupName().equals("")) {
				sql += " and group_id = '" + user.getGroupId() + "'";
			}
			sql += " limit ?,?";
			PreparedStatement ps = ct.prepareStatement(sql);
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setLoginName(rs.getString("username"));
				u.setId(rs.getString("id"));
				u.setName(rs.getString("name"));
				u.setPhoneNo(rs.getString("phone_no"));
				u.setGroupId(rs.getString("group_id"));
				u.setGroupName(rs.getString("groupname"));
				u.setEntryTime(rs.getString("entry_time"));
				u.setPhoneNo(rs.getString("phone_no"));
				u.setCardNo(rs.getString("card_no"));
				u.setUnit(rs.getString("unit"));
				u.setSex(rs.getString("sex"));
				u.setTel(rs.getString("tel"));
				list.add(u);
			}

			sql = "select count(*) as cc from t_user u left join t_group g on u.group_id = g.id where username <> 'admin' and username like '%"
					+ user.getLoginName()
					+ "%' and name like '%"
					+ user.getName() + "%' and u.status = '1'";
			if (!user.getGroupId().equals("")
					&& !user.getGroupName().equals("")) {
				sql += " and group_id = '" + user.getGroupId() + "'";
			}
			PreparedStatement ps2 = ct.prepareStatement(sql);
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				user.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("user", user);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/user/list");
	}

	@RequestMapping("/queryContact")
	public ModelAndView queryContact(User user, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<User> list = new ArrayList<User>();
		try {
			int index = user.getPageNum();
			int number = user.getEveryPage();
			int startindex = (index - 1) * number;
			String sql = "select * from t_user u left join t_group g on u.group_id = g.id where username <> 'admin' and username like '%"
					+ user.getLoginName()
					+ "%' and name like '%"
					+ user.getName() + "%' and u.status = '1'";
			if (!user.getGroupId().equals("")
					&& !user.getGroupName().equals("")) {
				sql += " and group_id = '" + user.getGroupId() + "'";
			}
			sql += " limit ?,?";
			PreparedStatement ps = ct.prepareStatement(sql);
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setLoginName(rs.getString("username"));
				u.setId(rs.getString("id"));
				u.setName(rs.getString("name"));
				u.setGroupId(rs.getString("group_id"));
				u.setGroupName(rs.getString("groupname"));
				u.setPhoneNo(rs.getString("phone_no"));
				list.add(u);
			}

			sql = "select count(*) as cc from t_user u left join t_group g on u.group_id = g.id where username <> 'admin' and username like '%"
					+ user.getLoginName()
					+ "%' and name like '%"
					+ user.getName() + "%' and u.status = '1'";
			if (!user.getGroupId().equals("")
					&& !user.getGroupName().equals("")) {
				sql += " and group_id = '" + user.getGroupId() + "'";
			}
			PreparedStatement ps2 = ct.prepareStatement(sql);
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				user.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("user", user);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/user/contact");
	}

	@RequestMapping("/new")
	public ModelAndView newUser(User user, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("insert into t_user(username,name,password,group_id,phone_no,entry_time,card_no,unit,sex,tel) values(?,?,md5("+user.getPassword()+"),?,?,?,?,?,?,?)");
			ps.setString(1, user.getLoginName());
			ps.setString(2, user.getName());
			ps.setString(3, user.getGroupId());
			ps.setString(4, user.getPhoneNo());
			ps.setString(5, user.getEntryTime());
			ps.setString(6, user.getCardNo());
			ps.setString(7, user.getUnit());
			ps.setString(8, user.getSex());
			ps.setString(9, user.getTel());
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/delete")
	public ModelAndView deleteUser(String id, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("update t_user set status = 0 where id = ?");
			ps.setString(1, id);
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/edit")
	public ModelAndView editUser(String id, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("select * from t_user u left join t_group g on u.group_id = g.id where u.id = ?");
			ps.setString(1, id);
			ResultSet rs = ps.executeQuery();
			User user = new User();
			user.setId(id);
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			if (rs.next()) {
				user.setLoginName(rs.getString("username"));
				user.setId(rs.getString("id"));
				user.setName(rs.getString("name"));
				user.setPhoneNo(rs.getString("phone_no"));
				user.setGroupId(rs.getString("group_id"));
				user.setGroupName(rs.getString("groupname"));
				user.setEntryTime(rs.getString("entry_time"));
				user.setPhoneNo(rs.getString("phone_no"));
				user.setCardNo(rs.getString("card_no"));
				user.setUnit(rs.getString("unit"));
				user.setSex(rs.getString("sex"));
				user.setTel(rs.getString("tel"));
			}
			request.setAttribute("user", user);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("/jsp/user/edit");
	}

	@RequestMapping("/modify")
	public ModelAndView modifyUser(User user, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("update t_user set username = ?,name=?,group_id=?,phone_no=?,entry_time=?,card_no=?,unit=?,tel=?,sex=? where id=?");
			ps.setString(1, user.getLoginName());
			ps.setString(2, user.getName());
			ps.setString(3, user.getGroupId());
			ps.setString(4, user.getPhoneNo());
			ps.setString(5, user.getEntryTime());
			ps.setString(6, user.getCardNo());
			ps.setString(7, user.getUnit());
			ps.setString(8, user.getTel());
			ps.setString(9, user.getSex());
			ps.setString(10, user.getId());
			int r = ps.executeUpdate();
			if (r > 0) {
				request.setAttribute("result", true);
			} else {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", "");
			}
			ps.close();
		} catch (Exception e) {
			request.setAttribute("result", false);
			request.setAttribute("errorMessage", e.getMessage());
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
				request.setAttribute("result", false);
				request.setAttribute("errorMessage", e.getMessage());
			}
		}
		return new ModelAndView("/jsp/sys/result");
	}

	@RequestMapping("/select")
	public ModelAndView select(HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		try {
			PreparedStatement ps = ct
					.prepareStatement("select * from t_user where status = '1' and username <>'admin'");
			ResultSet rs = ps.executeQuery();
			StringBuilder sb = new StringBuilder();
			sb.append("[");
			int i = 0;
			while (rs.next()) {
				if (i > 0) {
					sb.append(",");
				}
				sb.append("{\"userid\":\"");
				sb.append(rs.getString("id"));
				sb.append("\"");
				sb.append(",\"loginname\":\"");
				sb.append(rs.getString("username"));
				sb.append("\",\"username\":\"");
				sb.append(rs.getString("name"));
				sb.append("\"}");
				i++;
			}
			sb.append("]");
			request.setAttribute("info", sb.toString());
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("/jsp/sys/info");
	}

	@RequestMapping("/search")
	public ModelAndView search(User user, HttpServletRequest request) {
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<User> list = new ArrayList<User>();
		try {
			int index = user.getPageNum();
			int number = user.getEveryPage();
			int startindex = (index - 1) * number;
			PreparedStatement ps = ct
					.prepareStatement("select * from t_user where username <> 'admin' and name like '%"
							+ user.getName()
							+ "%' and username like '%"
							+ user.getLoginName()
							+ "%' and status = '1' limit ?,?");
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				User u = new User();
				u.setId(rs.getString("id"));
				u.setLoginName(rs.getString("username"));
				u.setName(rs.getString("name"));
				list.add(u);
			}

			PreparedStatement ps2 = ct
					.prepareStatement("select count(*) as cc from t_user where username <> 'admin' and name like '%"
							+ user.getName()
							+ "%' and username like '%"
							+ user.getLoginName() + "%' and status = '1'");
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				user.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("user", user);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/sys/userSelect");
	}
	
	@RequestMapping("/history")
	public ModelAndView history(LoginHistory loginHistory,HttpServletRequest request){
		ConnectionPool cp = ConnectionPool.getInstance();
		Connection ct = cp.getConnection();
		List<LoginHistory> list = new ArrayList<LoginHistory>();
		try {
			int index = loginHistory.getPageNum();
			int number = loginHistory.getEveryPage();
			int startindex = (index - 1) * number;
			String sql = "select * from t_login_history h left join t_user u on h.userid = u.id where u.username like '%"
					+ loginHistory.getUsername()
					+ "%' and u.name like '%"
					+ loginHistory.getName() + "%'";
			if(loginHistory.getLoginTimeS()!=null&&!loginHistory.getLoginTimeS().equals("")){
				sql += " and login_time>='" + loginHistory.getLoginTimeS() + "'";
			}
			if(loginHistory.getLoginTimeE()!=null&&!loginHistory.getLoginTimeE().equals("")){
				sql += " and login_time<='" + loginHistory.getLoginTimeE() + "'";
			}
			sql += " order by h.id desc";
			sql += " limit ?,?";
			PreparedStatement ps = ct.prepareStatement(sql);
			ps.setInt(1, startindex);
			ps.setInt(2, number);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				LoginHistory lh = new LoginHistory();
				lh.setUserid(rs.getString("userid"));
				lh.setUsername(rs.getString("username"));
				lh.setName(rs.getString("name"));
				lh.setLoginTime(rs.getString("login_time"));
				list.add(lh);
			}

			sql = "select count(*) as cc from t_login_history h left join t_user u on h.userid = u.id where u.username like '%"
					+ loginHistory.getUsername()
					+ "%' and u.name like '%"
					+ loginHistory.getName() + "%'";
			if(loginHistory.getLoginTimeS()!=null&&!loginHistory.getLoginTimeS().equals("")){
				sql += " and login_time>='" + loginHistory.getLoginTimeS() + "'";
			}
			if(loginHistory.getLoginTimeE()!=null&&!loginHistory.getLoginTimeE().equals("")){
				sql += " and login_time<='" + loginHistory.getLoginTimeE() + "'";
			}
			PreparedStatement ps2 = ct.prepareStatement(sql);
			ResultSet rs2 = ps2.executeQuery();
			if (rs2.next()) {
				loginHistory.setTotalCount(rs2.getInt("cc"));
			}

			request.setAttribute("loginHistory", loginHistory);
			request.setAttribute("list", list);
			rs.close();
			ps.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				ct.close();
			} catch (Exception e) {
			}
		}
		return new ModelAndView("jsp/user/history");
	}
}
